<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"wan-lei8.gitee.io","root":"/","scheme":"Pisces","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}}};
  </script>

  <meta name="description" content="Mysql索引Mysql的索引是一种加快查询速度的数据结构，索引就好比书的目录一样能够快速的定位你要查找的位置。 Mysql的索引底层是使用B+树的数据结构进行实现，结构如下图所示： 索引的一个数据页的大小是16kb，从磁盘加载到内存中是以数据页的大小为单位进行加载，然后供查询操作进行查询，若是查询的数据不在内存中，才会从磁盘中再次加载到内存中。 索引的实现有很多，比如hash。hash是以key">
<meta property="og:type" content="article">
<meta property="og:title" content="索引优化">
<meta property="og:url" content="https://wan-lei8.gitee.io/2022/01/24/%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96/index.html">
<meta property="og:site_name" content="程序员西桐">
<meta property="og:description" content="Mysql索引Mysql的索引是一种加快查询速度的数据结构，索引就好比书的目录一样能够快速的定位你要查找的位置。 Mysql的索引底层是使用B+树的数据结构进行实现，结构如下图所示： 索引的一个数据页的大小是16kb，从磁盘加载到内存中是以数据页的大小为单位进行加载，然后供查询操作进行查询，若是查询的数据不在内存中，才会从磁盘中再次加载到内存中。 索引的实现有很多，比如hash。hash是以key">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOEiaCXvzESCWNibctG2icZBE2WDwybNZdREF1KKhylLalkibsONGSA3icic4Q/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPO8CmxKpzIbky5Uez5hhCicyhrXR8tZDX8JAIPMicVK3TsDXs0ErV2eia0A/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOTwJRctD9VC5kQLCW0EQ6fXzs5WaO1dv1x7O5SJhoV5sA3GeXj4GibTQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOtTS6Nl7nB24icD1GypIYOQjAhmBxfjLv4Yb0NCjx3msGHice56LLHH5A/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOmiccvicv6Emql9KQHpJKHdDgOrFLJa0zEbeF7UogbuAss07EhTjbbibgQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOmiccvicv6Emql9KQHpJKHdDgOrFLJa0zEbeF7UogbuAss07EhTjbbibgQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOIsQbKUX5FeoS41iaen93ib21rLibbia8iaWmwaIicAWtvf6w2kp8Via9YnKNw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOqicjTkb53FdQcgtRhUwa8969O06AiaapyWXduPkUBOqiaibPcf7pkibkvFg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOYcjjEC0BicDFljIIG4Gj0IsLVmR1NePLQdZpcPDCbDSRTGoibs6BgSOg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOqJNdSVQY9KyGbBL6nAYBu7PbT700vdwlwYiaicotcFv7ojRMia6U9qtbQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOKrud7ezObIjoib7mDepOia3DHFXxic5vvf8reHzSzxUx5GzCulLdKM0Mw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOsNyscL1cV1R6Rxo26ITpQKBnmfldXrB7DhtY3rGd7s8Tia5flVuNbdw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOgTtIfy6TicsTHVOdTialouJUbeZ5ZKwvqZk1icflHzLZFLYVcvONcDPpA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="og:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPO37ribjawQYbBaMt2XMxnUMRzWeA3ibLJRS73zjyVLBRO6RTK8eQRvY8A/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">
<meta property="article:published_time" content="2022-01-24T08:38:13.000Z">
<meta property="article:modified_time" content="2022-01-24T08:38:42.282Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOEiaCXvzESCWNibctG2icZBE2WDwybNZdREF1KKhylLalkibsONGSA3icic4Q/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1">

<link rel="canonical" href="https://wan-lei8.gitee.io/2022/01/24/%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>索引优化 | 程序员西桐</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">程序员西桐</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
      <p class="site-subtitle" itemprop="description">但行好事，莫问前程</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a>

  </li>
  </ul>
</nav>




</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://wan-lei8.gitee.io/2022/01/24/%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.png">
      <meta itemprop="name" content="">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="程序员西桐">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          索引优化
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>
              

              <time title="创建时间：2022-01-24 16:38:13 / 修改时间：16:38:42" itemprop="dateCreated datePublished" datetime="2022-01-24T16:38:13+08:00">2022-01-24</time>
            </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h2 id="Mysql索引"><a href="#Mysql索引" class="headerlink" title="Mysql索引"></a>Mysql索引</h2><p>Mysql的索引是一种加快查询速度的数据结构，索引就好比书的目录一样能够快速的定位你要查找的位置。</p>
<p>Mysql的索引底层是使用B+树的数据结构进行实现，结构如下图所示：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOEiaCXvzESCWNibctG2icZBE2WDwybNZdREF1KKhylLalkibsONGSA3icic4Q/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片">索引的一个数据页的大小是16kb，从磁盘加载到内存中是以数据页的大小为单位进行加载，然后供查询操作进行查询，若是查询的数据不在内存中，才会从磁盘中再次加载到内存中。</p>
<p>索引的实现有很多，比如hash。hash是以key-value的形式进行存储，适合于等值查询的场景，查询的时间复杂度为O(1)，因为hash储存并不是有序的。</p>
<p>所以，对于范围查询就可能要遍历所有数据进行查询，而且不同值的计算还会出现hash冲突，所以hash并不适合于做Mysql的索引。</p>
<p>有序数组在等值查询和范围查询性能都是非常好的，那为什么又不用有序数组作为索引呢？因为对于数组而言作为索引更新的成本太高，新增数据要把后面的数据都往后移一位，所以也不采用有序数组作为索引的底层实现。</p>
<p>最后二叉树，主要是因为二叉树只有二叉，一个节点存储的数据量非常有限，需要频繁的随机IO读写磁盘，若是数据量大的情况下二叉的树高太高，严重影响性能，所以也不采用二叉树进行实现。</p>
<p>而B+树是多叉树，一个数据页的大小是16kb，在1-3的树高就能存储10亿级以上的数据，也就是只要访问磁盘1-3次就足够了，并且B+树的叶子结点上一个叶子结点有指针指向下一个叶子结点，便于范围查询：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPO8CmxKpzIbky5Uez5hhCicyhrXR8tZDX8JAIPMicVK3TsDXs0ErV2eia0A/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<h3 id="种类的索引"><a href="#种类的索引" class="headerlink" title="种类的索引"></a>种类的索引</h3><p>索引从数据结构进行划分的分为：<strong>B+树索引、hash索引、R-Tree索引、FULLTEXT索引</strong>。</p>
<p>索引从物理存储的角度划分为：<strong>聚族索引和非聚族索引</strong>。</p>
<p>从逻辑的角度分为：<strong>主键索引、普通索引、唯一索引、联合索引以及空间索引</strong>。</p>
<h2 id="什么是回表"><a href="#什么是回表" class="headerlink" title="什么是回表"></a>什么是回表</h2><p>再详细了解什么事回表之前，我们先来详细的深入了解一下什么是InnoDB的索引存储形式，InnoDB的主键索引存储形式是聚族索引，索引与数据都在一起：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOTwJRctD9VC5kQLCW0EQ6fXzs5WaO1dv1x7O5SJhoV5sA3GeXj4GibTQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片">InnoDB的主键索引中叶子结点并不是存储行指针，而是存储行数据，二级索引中MyISAM也是一样的存储方式，InnoDB的二级索引的叶子结点则是存储当前索引值以及对应的主键索引值。</p>
<p>InnoDB的二级索引带来的好处就是减少了由于数据移动或者数据页分列导致行数据的地址变了而带来的维护二级索引的性能开销，因为InnoDB的二级索引不需要更新行指针：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOtTS6Nl7nB24icD1GypIYOQjAhmBxfjLv4Yb0NCjx3msGHice56LLHH5A/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>上面说到InnoDB引擎的主键索引存储的是行数据，二级索引的叶子结点存储的是索引数据以及对应的主键，所以回表就是根据索引进行条件查询，回到主键索引树进行搜索的过程：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOmiccvicv6Emql9KQHpJKHdDgOrFLJa0zEbeF7UogbuAss07EhTjbbibgQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>因为查询还要回表一次，再次查询主键索引树，所以实际中应该尽量避免回表的产生。</p>
<h3 id="解决回表"><a href="#解决回表" class="headerlink" title="解决回表"></a>解决回表</h3><p>解决回表问题可以建立联合索引进行索引覆盖，如图所示根据name字段查询用户的name和sex属性出现了回表问题：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOmiccvicv6Emql9KQHpJKHdDgOrFLJa0zEbeF7UogbuAss07EhTjbbibgQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片">那么我们可以建立下面这个联合索引来解决：</p>
<figure class="highlight pgsql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="keyword">user</span> (</span><br><span class="line"> id <span class="type">int</span> <span class="keyword">primary key</span>,</span><br><span class="line"> <span class="type">name</span> <span class="type">varchar</span>(<span class="number">20</span>),</span><br><span class="line"> sex <span class="type">varchar</span>(<span class="number">5</span>),</span><br><span class="line"> <span class="keyword">index</span>(<span class="type">name</span>, sex)</span><br><span class="line">) engine = innodb;</span><br></pre></td></tr></table></figure>

<p>建立了如上所示的index(name,sex)联合索引，在二级索引的叶子结点的位置就会同时也出现sex字段的值，因为能够获取到要查询的所有字段，因为就不用再回表查询一次。</p>
<h2 id="最左前缀原则"><a href="#最左前缀原则" class="headerlink" title="最左前缀原则"></a>最左前缀原则</h2><p>最左前缀原则可以是联合索引的的最左N个字段，也可以是字符串索引的最左的M个字符。举个例子，假如现在有一个表的原始数据如下所示：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOIsQbKUX5FeoS41iaen93ib21rLibbia8iaWmwaIicAWtvf6w2kp8Via9YnKNw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>并根据col3 ，col2的顺序建立联合索引，此时联合索引树结构如图下所示：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOqicjTkb53FdQcgtRhUwa8969O06AiaapyWXduPkUBOqiaibPcf7pkibkvFg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>叶子结点中首先会根据col3的字符进行排序，若是col3相等，在col3相等的值里面再对col2进行排序，假如我们要查询where col3 like ‘Eri%’，就可以快速的定位查询到Eric。</p>
<p>若是查询条件为where col3 like ‘%se’，前面的字符不确定，表示任意字符都可以，这样就可以导致全表扫描进行字符的比较，就会使索引失效。</p>
<h2 id="调优案例一"><a href="#调优案例一" class="headerlink" title="调优案例一"></a>调优案例一</h2><p>其中第一个案例新人写了一个这样的sql，由于业务的原因就不粘贴全部sql，其中sql的条件如下所示<strong>WHERE ( STATUS = ‘1’ AND shop_code = ‘XXX’ )  GROUP BY act_id</strong>。</p>
<p>并且他在表中建立的这样的一个索引<strong>idx_status_shop_code</strong>，当时看到就吐血一地。</p>
<p>我给新人提出建议是把shop_code为’’空字符串的（数据库默认值）是设为一个特殊的00000这样的类型，不要把商店的shop_code默认值设置为空字符串。</p>
<p>并且，索引中shop_code字段放在前面，建<strong>idx_shop_code_status_act_id</strong>索引。</p>
<p><strong>因为一般状态值status只有0和1，区分度不大，而shop_code的区分度大，在执行where条件筛选的时候，区分度大的放在前面，第一层过滤的时候就能过滤掉大部分行数，减少扫描的行数，提高效率。</strong></p>
<p>并且将act_id，和store_code也加入其中，sql中涉及分组（group by）操作，这样就能避免filesort排序，因为索引本来就是有序的：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOYcjjEC0BicDFljIIG4Gj0IsLVmR1NePLQdZpcPDCbDSRTGoibs6BgSOg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>并且从他的sql中可以看到他只要返回act_id，而实际中只用到了act_id，所以将act_id也加入索引中就可以避免回表的操作，所以再索引中最后加入act_id有两大好处：<strong>避免回表以及避免filesort排序</strong>。</p>
<p>因为写sql的不良习惯造成回表操作，平时也没有注意建立索引的一些原则，以及理解索引的一些原理，所以新人对于一些优化的理解还有要一步一步的指导，毕竟自己也是从新人过来的。</p>
<p>对于索引的顺序的建立，以及出现filesort的解决方案在阿里的开发手册中也有提到：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOqJNdSVQY9KyGbBL6nAYBu7PbT700vdwlwYiaicotcFv7ojRMia6U9qtbQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>order by和group by都可以利用索引的有序性。</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOKrud7ezObIjoib7mDepOia3DHFXxic5vvf8reHzSzxUx5GzCulLdKM0Mw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>说真的这本开发手册还是非常好用的，里面很多经验总结，慢慢的遇到场景就能够瞬间顿悟，毕竟是众多阿里人的开发经验的结晶。</p>
<h2 id="调优案例二"><a href="#调优案例二" class="headerlink" title="调优案例二"></a>调优案例二</h2><p>第二个案例是关于字符串的，新人接到一个需求需要比较电话，但是一般电话在数据库中都会加密md5。</p>
<p>所以，新人为了提高查询的效率新建KEY <code>idx_mobile_md5_type</code> (<code>mobile_md5</code>,<code>type</code>)使用md5全字段建立索引。</p>
<p>我是建议他使用<strong>select count(distinct left(mobile_md5, 5))/count(*) from XXX.users</strong>查找最大的区分度：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOsNyscL1cV1R6Rxo26ITpQKBnmfldXrB7DhtY3rGd7s8Tia5flVuNbdw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>在实际中当md5值长度为5以及大于5的长度都不变了：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPOgTtIfy6TicsTHVOdTialouJUbeZ5ZKwvqZk1icflHzLZFLYVcvONcDPpA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<p>实际情况只要前五个字符就能达到80%的区分度，并且再加字段长度区分度也不变，所以个人提出只要建立前五个字符的索引即可，可以大大节约空间。</p>
<p>这个在阿里的开发手册也有提到，其实一般来说达到90%的区分度是比较好的，区分度越大，就类似于越趋向于唯一索引，过滤的行数就越多：</p>
<p><img src="https://mmbiz.qpic.cn/mmbiz_png/IJUXwBNpKlg5vibfMDibOVCic39xwMJylPO37ribjawQYbBaMt2XMxnUMRzWeA3ibLJRS73zjyVLBRO6RTK8eQRvY8A/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" alt="图片"></p>
<h2 id="调优案例三"><a href="#调优案例三" class="headerlink" title="调优案例三"></a>调优案例三</h2><p>最后一个字符串的案例就是userId，这个userId使用有20位的长度的字符串左右，有点类似于身份证号码，大家都知道身份证号码的前多少位是基本一样的，区别大的在后面的几位（具体几位没去了解过）。</p>
<p>我们这边的场景也是一样，userId前10位左右基本都是一样，反而只有后面的几位区别度高达90%以上。</p>
<p>所以，建议新人建立userId的反转之后的前几位索引即可，区别度可以通过：<strong>select count(distinct left(REVERSE(userId), 7))/count(*) as ‘区分度’ from XXX.users;</strong></p>
<p>具体sql如下的sql：<strong>select u.city_code from XXX.city_role_user u where  role_key = ‘XXX’ and uc_id = ‘XXX’ and status = 1;</strong></p>
<p>这个sql有两个问题，一个是把区分度不大的role_key放在前面，因为一般角色key在PC端只有几种，区别度很小；另一个就是前面说的uc_id字符串问题。</p>
<p>我是建议把where条件的条件uc_id放在前面，建立索引也是如此，并且uc_id是由20位的数字组成，前面的10位左右都是一样的，只有后面的几位区分度才大。</p>
<p>所以个人也建议通过查询区分度，并且建立翻转字符串后的索引来达到节省空间，并且还可以提升查询效率，最后就是city_code也加入索引中建立联合索引就可以避免回表操作。</p>
<p>所以，这就要sql优化的关键点有三个：<strong>区分度大的放在前面、字符串减少长度、避免回表</strong>。</p>
<h2 id="其它的code-review"><a href="#其它的code-review" class="headerlink" title="其它的code review"></a>其它的code review</h2><p>通过code review新人的代码，还发现一些问题，就是不遵循接口的单一原则，比较喜欢写通用的接口，一个接口多个场景使用，通常使用select * 返回数据，对于一些where条件的查询需要大量的回表操作，但是一些接口中只需要用到其中select 回来的一个字段，所以导致慢sql，慢接口的产生。</p>
<p>并且，在实际的编码中主要是面向于实现，对于一些整体的模块没有把控，类似于一些可以使用到<strong>策略模式、建造者模式</strong>等设计模式的，都没有使用，代码的扩展性比较差。</p>
<p>还要在代码中大量的使用Java 8的stream流操作，代码的可读性差，对于stream流其实可以用来并行流处理还是挺高效的，因为stream流的底层使用到了Fork/Join。</p>
<p>在服务器配置允许的条件下，使用如下代码，数据量大的时候是可以有效率提升的，下面引用redspider的一个案例：</p>
<figure class="highlight reasonml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line">public <span class="keyword">class</span> StreamParallelDemo &#123;</span><br><span class="line">    public static void main(String<span class="literal">[]</span> args) &#123;</span><br><span class="line">        <span class="module-access"><span class="module"><span class="identifier">System</span>.</span></span>out.println(<span class="module-access"><span class="module"><span class="identifier">String</span>.</span></span>format(<span class="string">&quot;本计算机的核数：%d&quot;</span>, <span class="module-access"><span class="module"><span class="identifier">Runtime</span>.</span></span>get<span class="constructor">Runtime()</span>.available<span class="constructor">Processors()</span>));</span><br><span class="line"></span><br><span class="line">        <span class="comment">// 产生100w个随机数(1 ~ 100)，组成列表</span></span><br><span class="line">        Random random = <span class="keyword">new</span> <span class="constructor">Random()</span>;</span><br><span class="line">        List&lt;Integer&gt; <span class="built_in">list</span> = <span class="keyword">new</span> ArrayList&lt;&gt;(<span class="number">1000_0000</span>);</span><br><span class="line"></span><br><span class="line">        <span class="keyword">for</span> (<span class="built_in">int</span> i = <span class="number">0</span>; i &lt; <span class="number">1000_0000</span>; i++) &#123;</span><br><span class="line">            <span class="built_in">list</span>.add(random.next<span class="constructor">Int(100)</span>);</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        long prevTime = get<span class="constructor">CurrentTime()</span>;</span><br><span class="line">        <span class="built_in">list</span>.stream<span class="literal">()</span>.reduce((a, b) -&gt; a + b).<span class="keyword">if</span><span class="constructor">Present(System.<span class="params">out</span>::<span class="params">println</span>)</span>;</span><br><span class="line">        <span class="module-access"><span class="module"><span class="identifier">System</span>.</span></span>out.println(<span class="module-access"><span class="module"><span class="identifier">String</span>.</span></span>format(<span class="string">&quot;单线程计算耗时：%d&quot;</span>, get<span class="constructor">CurrentTime()</span> - prevTime));</span><br><span class="line"></span><br><span class="line">        prevTime = get<span class="constructor">CurrentTime()</span>;</span><br><span class="line">        <span class="built_in">list</span>.stream<span class="literal">()</span>.parallel<span class="literal">()</span>.reduce((a, b) -&gt; a + b).<span class="keyword">if</span><span class="constructor">Present(System.<span class="params">out</span>::<span class="params">println</span>)</span>;</span><br><span class="line">        <span class="module-access"><span class="module"><span class="identifier">System</span>.</span></span>out.println(<span class="module-access"><span class="module"><span class="identifier">String</span>.</span></span>format(<span class="string">&quot;多线程计算耗时：%d&quot;</span>, get<span class="constructor">CurrentTime()</span> - prevTime));</span><br><span class="line"></span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> static long get<span class="constructor">CurrentTime()</span> &#123;</span><br><span class="line">        return <span class="module-access"><span class="module"><span class="identifier">System</span>.</span></span>current<span class="constructor">TimeMillis()</span>;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p>一路code review，发现还是挺多问题，也是非常基础的东西，这里就顺手做了个记录，不过也情有可原，毕竟是新人，只能慢慢的指导，一行代码一行代码的手把手教。</p>

    </div>
<div>
  
    <div>
    
        <div style="text-align:center;color: #ccc;font-size:14px;">-------------本文结束<i class="fa fa-paw"></i>感谢您的阅读-------------</div>
    
</div>

  
</div>

    
    
    
        

<div>
<ul class="post-copyright">
  <li class="post-copyright-author">
    <strong>本文作者： </strong>
  </li>
  <li class="post-copyright-link">
    <strong>本文链接：</strong>
    <a href="https://wan-lei8.gitee.io/2022/01/24/%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96/" title="索引优化">https://wan-lei8.gitee.io/2022/01/24/索引优化/</a>
  </li>
  <li class="post-copyright-license">
    <strong>版权声明： </strong>本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" rel="noopener" target="_blank"><i class="fab fa-fw fa-creative-commons"></i>BY-NC-SA</a> 许可协议。转载请注明出处！
  </li>
</ul>
</div>


      <footer class="post-footer">

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/2022/01/24/%E7%BB%99%E4%BD%A0%E7%9A%84Java%E7%A8%8B%E5%BA%8F%E6%8B%8D%E4%B8%AA%E7%89%87%E5%AD%90%E5%90%A7-jstack%E5%91%BD%E4%BB%A4%E8%A7%A3%E6%9E%90/" rel="prev" title="给你的Java程序拍个片子吧:jstack命令解析">
      <i class="fa fa-chevron-left"></i> 给你的Java程序拍个片子吧:jstack命令解析
    </a></div>
      <div class="post-nav-item">
    <a href="/2022/01/24/%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BC%98%E5%8C%96%EF%BC%8C%E5%8F%AF%E4%BB%A5%E4%BB%8E%E5%93%AA%E4%BA%9B%E7%BB%B4%E5%BA%A6%E5%85%A5%E6%89%8B/" rel="next" title="数据库优化，可以从哪些维度入手">
      数据库优化，可以从哪些维度入手 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#Mysql%E7%B4%A2%E5%BC%95"><span class="nav-number">1.</span> <span class="nav-text">Mysql索引</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%A7%8D%E7%B1%BB%E7%9A%84%E7%B4%A2%E5%BC%95"><span class="nav-number">1.1.</span> <span class="nav-text">种类的索引</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E4%BB%80%E4%B9%88%E6%98%AF%E5%9B%9E%E8%A1%A8"><span class="nav-number">2.</span> <span class="nav-text">什么是回表</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%A7%A3%E5%86%B3%E5%9B%9E%E8%A1%A8"><span class="nav-number">2.1.</span> <span class="nav-text">解决回表</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9C%80%E5%B7%A6%E5%89%8D%E7%BC%80%E5%8E%9F%E5%88%99"><span class="nav-number">3.</span> <span class="nav-text">最左前缀原则</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%B0%83%E4%BC%98%E6%A1%88%E4%BE%8B%E4%B8%80"><span class="nav-number">4.</span> <span class="nav-text">调优案例一</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%B0%83%E4%BC%98%E6%A1%88%E4%BE%8B%E4%BA%8C"><span class="nav-number">5.</span> <span class="nav-text">调优案例二</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E8%B0%83%E4%BC%98%E6%A1%88%E4%BE%8B%E4%B8%89"><span class="nav-number">6.</span> <span class="nav-text">调优案例三</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%85%B6%E5%AE%83%E7%9A%84code-review"><span class="nav-number">7.</span> <span class="nav-text">其它的code review</span></a></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt=""
      src="/images/avatar.png">
  <p class="site-author-name" itemprop="name"></p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives">
          <span class="site-state-item-count">41</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
  </nav>
</div>
  <div class="links-of-author motion-element">
      <span class="links-of-author-item">
        <a href="https://github.com/yourname" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;yourname" rel="noopener" target="_blank"><i class="fab fa-github fa-fw"></i>GitHub</a>
      </span>
      <span class="links-of-author-item">
        <a href="/malegod958@163.com" title="E-Mail → malegod958@163.com"><i class="fa fa-envelope fa-fw"></i>E-Mail</a>
      </span>
  </div>
  <div class="cc-license motion-element" itemprop="license">
    <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" class="cc-opacity" rel="noopener" target="_blank"><img src="/images/cc-by-nc-sa.svg" alt="Creative Commons"></a>
  </div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 2018 – 
  <span itemprop="copyrightYear">2022</span>
  <span class="with-love">
    <i class="heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">万磊</span>
</div>
  <div class="powered-by">

  </div>

        








      </div>
    </footer>
  </div>

  
  
  <script color='' opacity='0.5' zIndex='-1' count='99' src="/lib/canvas-nest/canvas-nest.min.js"></script>
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/pisces.js"></script>


<script src="/js/next-boot.js"></script>




  















  

  

  
	<script type="text/javascript" src="//cdn.bootcss.com/canvas-nest.js/1.0.0/canvas-nest.min.js"></script>
	

<script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"log":false,"pluginJsPath":"lib/","pluginModelPath":"assets/","pluginRootPath":"live2dw/","tagMode":false});</script></body>
</html>
<!-- 页面点击小红心 -->
<script type="text/javascript" src="/js/src/clicklove.js"></script>
